package com.itany.corejava.code13_jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author MissChen
 * @version 1.0
 * @date 2023 年12月12日 14:47
 */
public class Test07_学生管理系统 {
    public static void main(String[] args) {
        Student student=new Student("wbs103","tom2",22);
        try {
            // insertStudent(student);
            // deleteStudentByNo("wbs101");
            // updateStudentByNo(new Student("wbs102","aa",25));
            // List<Student> students=queryAll();
            // for(Student stu:students){
            //     System.out.println(stu);
            // }

            // Student stu=queryStudentByNo("wbs102");
            // System.out.println(stu);

            // List<Student> students=queryStudentByName("%");
            // System.out.println(students);

            List<Student> students=queryStudentByAge(20,22);
            System.out.println(students);
            System.out.println(students.size());

        } catch (DataAccessException e) {

            System.out.println(e.getMessage());
        }
    }

    /**
     * 添加学生信息
     * @param student
     * @throws DataAccessException
     */
    public static void insertStudent(Student student) throws DataAccessException {
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            conn=JDBCUtil.getConnection();
            String sql=new StringBuffer()
                    .append(" insert into ")
                    .append(" t_student ")
                    .append(" (no,name,age) ")
                    .append(" values (?,?,?) ")
                    .toString();
            ps=conn.prepareStatement(sql);
            ps.setString(1,student.getNo());
            ps.setString(2,student.getName());
            ps.setInt(3,student.getAge());
            int row=ps.executeUpdate();//执行DML操作
            System.out.println("数据库中有"+row+" rows affected");

        } catch (DataAccessException e) {
            throw  e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("数据库访问失败");
        } finally {
            JDBCUtil.close(conn,ps,null);
        }
    }


    /**
     * 根据学号删除学生
     * @param no
     */
    public static void deleteStudentByNo(String no) throws DataAccessException {
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            conn=JDBCUtil.getConnection();
            String sql=new StringBuffer()
                    .append("delete from t_student ")
                    .append(" where no=? ")
                    .toString();
            ps=conn.prepareStatement(sql);
            ps.setString(1,no);
            int row=ps.executeUpdate();//执行DML操作
            System.out.println("数据库中有"+row+" rows affected");


        } catch (DataAccessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("数据库访问异常");
        } finally {
            JDBCUtil.close(conn,ps,null);
        }
    }

    /**
     * 根据学号进行修改学生信息
     * @param student
     * @throws DataAccessException
     */
    public static void updateStudentByNo(Student student) throws DataAccessException {
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            conn=JDBCUtil.getConnection();
            String sql=new StringBuffer()
                    .append("update t_student set ")
                    .append(" name=?,age=? ")
                    .append(" where no=? ")
                    .toString();
            ps=conn.prepareStatement(sql);

            ps.setString(1,student.getName());
            ps.setInt(2,student.getAge());
            ps.setString(3,student.getNo());
            int row=ps.executeUpdate();//执行DML操作
            System.out.println("数据库中有"+row+" rows affected");

        } catch (DataAccessException e) {
            throw  e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("数据库访问失败");
        } finally {
            JDBCUtil.close(conn,ps,null);
        }
    }

    /**
     * 查询所有学生的信息
     * @return
     * @throws DataAccessException
     */
    public static List<Student> queryAll() throws DataAccessException {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        List<Student> students=new ArrayList<>();
        try {
            conn=JDBCUtil.getConnection();
            String sql=new StringBuffer()
                    .append("select id,no,name,age ")
                    .append(" from t_student ")
                    .toString();
            ps=conn.prepareStatement(sql);


            rs=ps.executeQuery();//执行DML操作
            while(rs.next()){
                Student student=new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                students.add(student);
            }

        } catch (DataAccessException e) {
            throw  e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("数据库访问失败");
        } finally {
            JDBCUtil.close(conn,ps,rs);
        }
        return students;
    }

    /**
     * 根据学号进行查询学生
     * @param no
     * @return
     * @throws DataAccessException
     */
    public static  Student queryStudentByNo(String no) throws DataAccessException {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        Student student=null;
        try {
            conn=JDBCUtil.getConnection();
            String sql=new StringBuffer()
                    .append("select id,no,name,age ")
                    .append(" from t_student ")
                    .append(" where no=? ")
                    .toString();
            ps=conn.prepareStatement(sql);
            ps.setString(1,no);

            rs=ps.executeQuery();//执行DML操作
            while(rs.next()){
                student=new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));

            }

        } catch (DataAccessException e) {
            throw  e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("数据库访问失败");
        } finally {
            JDBCUtil.close(conn,ps,rs);
        }
        return student;
    }

    /**
     * 根据名字模糊查找学生
     * @param name
     * @return
     * @throws DataAccessException
     */
    public static List<Student> queryStudentByName(String name) throws DataAccessException {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        List<Student> students=new ArrayList<>();

        // select * from t_student where name like '%/%%' escape '/';
        // escape后面的指定的是转义符
        try {
            conn=JDBCUtil.getConnection();
            String sql=new StringBuffer()
                    .append("select id,no,name,age ")
                    .append(" from t_student ")
                    .append(" where name like ?")
                    .append(" escape '/'")
                    .toString();
            ps=conn.prepareStatement(sql);
            ps.setString(1,"%/"+name+"%");


            rs=ps.executeQuery();//执行DML操作
            while(rs.next()){
                Student student=new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                students.add(student);
            }

        } catch (DataAccessException e) {
            throw  e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("数据库访问失败");
        } finally {
            JDBCUtil.close(conn,ps,rs);
        }
        return students;
    }

    public static List<Student> queryStudentByAge(int minAge,int maxAge) throws DataAccessException {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        List<Student> students=new ArrayList<>();

        // select * from t_student where name like '%/%%' escape '/';
        // escape后面的指定的是转义符
        try {
            conn=JDBCUtil.getConnection();
            String sql=new StringBuffer()
                    .append("select id,no,name,age ")
                    .append(" from t_student ")
                    .append(" where age between ? and ? ")
                    .toString();
            ps=conn.prepareStatement(sql);
            ps.setInt(1,minAge);
            ps.setInt(2,maxAge);

            rs=ps.executeQuery();//执行DML操作
            while(rs.next()){
                Student student=new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                students.add(student);
            }

        } catch (DataAccessException e) {
            throw  e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("数据库访问失败");
        } finally {
            JDBCUtil.close(conn,ps,rs);
        }
        return students;
    }



}
